[CmdletBinding()]
param(
    [string]$MySQLVersion = "9.3",
    [string]$InstallPath = "C:\Program Files\MySQL",
    [string]$DataPath = "D:\MySQLData",
    [string]$Port = "3306",
    [string]$RootPassword = "123456",
    [switch]$SkipValidation,
    [switch]$Force,
    [string]$InstallerDirectory = ".\Installer"
)

# Log function
function Write-Log {
    param(
        [string]$Message,
        [string]$Level = "INFO"
    )
    
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] [$Level] $Message"
    
    $color = switch ($Level) {
        "ERROR" { "Red" }
        "WARN" { "Yellow" }
        "SUCCESS" { "Green" }
        default { "White" }
    }
    
    Write-Host $logMessage -ForegroundColor $color
}

# Check administrator privileges
function Test-Administrator {
    $currentUser = [Security.Principal.WindowsIdentity]::GetCurrent()
    $principal = New-Object Security.Principal.WindowsPrincipal($currentUser)
    return $principal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
}

# Check internet connection
function Test-InternetConnection {
    try {
        $response = Invoke-WebRequest -Uri "https://www.baidu.com" -TimeoutSec 5 -UseBasicParsing
        return $response.StatusCode -eq 200
    }
    catch {
        return $false
    }
}

# Check disk space
function Test-DiskSpace {
    param(
        [string]$Drive = "D:",
        [long]$RequiredSpaceGB = 10
    )
    
    $driveInfo = Get-WmiObject -Class Win32_LogicalDisk -Filter "DeviceID='$Drive'"
    if ($driveInfo) {
        $freeSpaceGB = [math]::Round($driveInfo.FreeSpace / 1GB, 2)
        return @{
            HasEnoughSpace = $freeSpaceGB -ge $RequiredSpaceGB
            FreeSpaceGB = $freeSpaceGB
            RequiredSpaceGB = $RequiredSpaceGB
        }
    }
    return @{
        HasEnoughSpace = $false
        FreeSpaceGB = 0
        RequiredSpaceGB = $RequiredSpaceGB
    }
}

# Check existing MySQL installation
function Test-MySQLInstallation {
    param([string]$MySQLPath = "C:\MySQL")
    
    # Clear any polluted variables
    Remove-Variable mysqlExe -ErrorAction SilentlyContinue
    Remove-Variable mysqldExe -ErrorAction SilentlyContinue
    
    $mysqlExe = Join-Path $MySQLPath "bin\mysql.exe"
    $mysqldExe = Join-Path $MySQLPath "bin\mysqld.exe"
    
    $results = @{
        MySQLExeExists = Test-Path $mysqlExe
        MySQLDExeExists = Test-Path $mysqldExe
        ServiceExists = Get-Service -Name "MySQL" -ErrorAction SilentlyContinue
        ServiceRunning = $false
        PortAvailable = $false
    }
    
    if ($results.ServiceExists) {
        $results.ServiceRunning = (Get-Service -Name "MySQL").Status -eq "Running"
    }
    
    # Check if port is available
    try {
        $tcpClient = New-Object System.Net.Sockets.TcpClient
        $tcpClient.Connect("localhost", 3306)
        $results.PortAvailable = $true
        $tcpClient.Close()
    }
    catch {
        $results.PortAvailable = $false
    }
    
    return $results
}

# Find MySQL installer in local directory
function Find-MySQLInstaller {
    param(
        [string]$InstallerDirectory,
        [string]$MySQLVersion
    )
    
    Write-Log "Searching for MySQL installer in local directory: $InstallerDirectory" "INFO"
    
    # Check if installer directory exists
    if (!(Test-Path $InstallerDirectory)) {
        Write-Log "Installer directory not found: $InstallerDirectory" "ERROR"
        return $null
    }
    
    # Look for MySQL installer files
    $installerPatterns = @(
        "mysql-installer-community-$MySQLVersion*.msi",
        "mysql-installer-community-*.msi",
        "mysql-installer-*.msi",
        "mysql-*.msi"
    )
    
    foreach ($pattern in $installerPatterns) {
        $foundFiles = Get-ChildItem -Path $InstallerDirectory -Filter $pattern -ErrorAction SilentlyContinue
        if ($foundFiles) {
            $installerFile = $foundFiles | Sort-Object LastWriteTime -Descending | Select-Object -First 1
            Write-Log "Found MySQL installer: $($installerFile.Name)" "SUCCESS"
            Write-Log "File size: $([math]::Round($installerFile.Length / 1MB, 2)) MB" "INFO"
            Write-Log "Last modified: $($installerFile.LastWriteTime)" "INFO"
            return $installerFile.FullName
        }
    }
    
    Write-Log "No MySQL installer found in directory: $InstallerDirectory" "ERROR"
    Write-Log "Expected file patterns:" "INFO"
    foreach ($pattern in $installerPatterns) {
        Write-Log "  - $pattern" "INFO"
    }
    
    return $null
}

# Find actual MySQL installation path after MSI installation
function Find-ActualMySQLPath {
    Write-Log "Searching for actual MySQL installation path..." "INFO"
    
    # Common MySQL installation paths
    $commonPaths = @(
        "C:\MySQL",
        "C:\Program Files\MySQL",
        "C:\Program Files (x86)\MySQL",
        "C:\mysql",
        "D:\MySQL"
    )
    
    # Search for mysqld.exe in common paths
    foreach ($path in $commonPaths) {
        if (Test-Path $path) {
            # Check for MySQL Server subdirectories
            $mysqlDirs = Get-ChildItem -Path $path -Directory -ErrorAction SilentlyContinue
            foreach ($dir in $mysqlDirs) {
                $mysqldPath = Join-Path $dir.FullName "bin\mysqld.exe"
                if (Test-Path $mysqldPath) {
                    Write-Log "Found MySQL installation: $($dir.FullName)" "SUCCESS"
                    return $dir.FullName
                }
            }
            
            # Check direct path
            $mysqldPath = Join-Path $path "bin\mysqld.exe"
            if (Test-Path $mysqldPath) {
                Write-Log "Found MySQL installation: $path" "SUCCESS"
                return $path
            }
        }
    }
    
    # Search for mysqld.exe in entire C: drive (fallback)
    Write-Log "Searching for mysqld.exe in C: drive..." "INFO"
    $mysqldFiles = Get-ChildItem -Path "C:\" -Recurse -Filter "mysqld.exe" -ErrorAction SilentlyContinue | Select-Object -First 5
    foreach ($file in $mysqldFiles) {
        $mysqlDir = Split-Path (Split-Path $file.FullName -Parent) -Parent
        Write-Log "Found MySQL installation: $mysqlDir" "SUCCESS"
        return $mysqlDir
    }
    
    Write-Log "Could not find MySQL installation path" "ERROR"
    return $null
}

# Main installation function
function Install-MySQL {
    param(
        [string]$MySQLVersion,
        [string]$InstallPath,
        [string]$DataPath,
        [string]$Port,
        [string]$RootPassword,
        [string]$InstallerPath
    )
    
    Write-Log "Starting MySQL $MySQLVersion installation..." "INFO"
    
    # Create installation directories
    Write-Log "Creating installation directories..." "INFO"
    New-Item -ItemType Directory -Path $InstallPath -Force | Out-Null
    New-Item -ItemType Directory -Path $DataPath -Force | Out-Null
    
    # Verify installer file exists
    if (!(Test-Path $InstallerPath)) {
        throw "MySQL installer not found at: $InstallerPath"
    }
    
    Write-Log "Using local MySQL installer: $InstallerPath" "INFO"
    
    # Silent install MySQL
    Write-Log "Installing MySQL..." "INFO"
    $installArgs = "/i `"$InstallerPath`" /quiet /norestart"
    $process = Start-Process msiexec.exe -ArgumentList $installArgs -Wait -PassThru
    
    if ($process.ExitCode -ne 0) {
        Write-Log "MySQL installation failed, exit code: $($process.ExitCode)" "ERROR"
        throw "MySQL installation failed"
    }
    
    Write-Log "MySQL installer installation completed" "SUCCESS"
    
    # Find actual MySQL installation path
    $actualMySQLPath = Find-ActualMySQLPath
    if (!$actualMySQLPath) {
        throw "Could not find MySQL installation after MSI installation"
    }
    
    Write-Log "Using actual MySQL installation path: $actualMySQLPath" "INFO"
    
    # Create MySQL configuration file with Chinese support
    Write-Log "Creating MySQL configuration file with Chinese support..." "INFO"
    
    # Use the same data directory structure as the working installation
    $dataDir = "D:/ProgramData/MySQL/MySQL Server 9.3/Data"
    
    $MySQLConfig = @"
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by MySQL Installation Script with Chinese Support
#
# Installation Instructions
# ----------------------------------------------------------------------
# On Windows, when MySQL has been installed using MySQL Installer you
# should keep this file in the ProgramData directory of your server
# (e.g. C:\ProgramData\MySQL\MySQL Server X.Y). To make sure the server
# reads the config file, use the startup option "--defaults-file".
#
# To run the server from the command line, execute this in a
# command line shell, e.g.
# mysqld --defaults-file="$actualMySQLPath\my.ini"
#
# To install the server as a Windows service manually, execute this in a
# command line shell, e.g.
# mysqld --install MySQL --defaults-file="$actualMySQLPath\my.ini"
#
# And then execute this in a command line shell to start the server, e.g.
# net start MySQL
#
# CLIENT SECTION
# ----------------------------------------------------------------------
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
port=3306

[mysql]
no-beep
default-character-set=utf8mb4

# SERVER SECTION
# ----------------------------------------------------------------------
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]
# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory

# shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use.
# socket=MYSQL

# The access control granted to clients on the named pipe created by the MySQL Server.
# named-pipe-full-access-group=

# The TCP/IP Port the MySQL Server will listen on
port=$Port

# Path to installation directory. All paths are usually resolved relative to this.
basedir="$actualMySQLPath"

# Path to the database root
datadir=$dataDir

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# The current server SQL mode, which can be set dynamically.
# Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This
# makes it easier to use MySQL in different environments and to use MySQL together with other
# database servers.
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="mysql.log"
slow-query-log=1
slow_query_log_file="mysql-slow.log"
long_query_time=10

# Error Logging.
log-error="mysql_error.log"

# Binary Logging.
log-bin="mysql-bin"
server-id=1

# Indicates how table and database names are stored on disk and used in MySQL.
lower_case_table_names=1

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=151

# The number of open tables for all threads. Increasing this value increases the number
# of file descriptors that mysqld requires.
table_open_cache=4000

# Defines the maximum amount of memory that can be occupied by the TempTable
# storage engine before it starts storing data on disk.
temptable_max_ram=1G

# Defines the maximum size of internal in-memory temporary tables created
# by the MEMORY storage engine and, as of MySQL 8.0.28, the TempTable storage
# engine. If an internal in-memory temporary table exceeds this size, it is
# automatically converted to an on-disk internal temporary table.
tmp_table_size=132M

# The storage engine for in-memory internal temporary tables
internal_tmp_mem_storage_engine=TempTable

# MyISAM Specific options
myisam_max_sort_file_size=2146435072
myisam_sort_buffer_size=253M
key_buffer_size=8M
read_buffer_size=128K
read_rnd_buffer_size=256K

# InnoDB Specific options
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=128M
innodb_redo_log_capacity=100M
innodb_thread_concurrency=29
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
flush_time=0
join_buffer_size=256K
max_allowed_packet=64M
max_connect_errors=100
open_files_limit=8161
sort_buffer_size=256K
binlog_row_event_max_size=8K
sync_source_info=10000
sync_relay_log=10000

# MySQL X Protocol
mysqlx_port=33060
"@

   # Create data directory if it doesn't exist
   $dataDirPath = $dataDir -replace "/", "\"
   New-Item -ItemType Directory -Path $dataDirPath -Force | Out-Null
   
   # Write configuration file using .NET to ensure no BOM
   $bytes = [System.Text.Encoding]::UTF8.GetBytes($MySQLConfig)
   [System.IO.File]::WriteAllBytes("$actualMySQLPath\my.ini", $bytes)
   Write-Log "MySQL configuration file with Chinese support created successfully" "SUCCESS"
    
    # Initialize MySQL database
    Write-Log "Initializing MySQL database..." "INFO"
    $mysqldPath = Join-Path $actualMySQLPath "bin\mysqld.exe"
    $initArgs = "--initialize-insecure --user=mysql --datadir=$dataDir"
    $process = Start-Process $mysqldPath -ArgumentList $initArgs -Wait -PassThru
    
    if ($process.ExitCode -ne 0) {
        Write-Log "MySQL initialization failed" "ERROR"
        throw "MySQL initialization failed"
    }
    
    Write-Log "MySQL database initialization completed" "SUCCESS"
    
    # Install Windows service
    Write-Log "Installing MySQL Windows service..." "INFO"
    $serviceArgs = "--install MySQL --defaults-file=`"$actualMySQLPath\my.ini`""
    $process = Start-Process $mysqldPath -ArgumentList $serviceArgs -Wait -PassThru
    
    if ($process.ExitCode -ne 0) {
        Write-Log "MySQL service installation failed" "ERROR"
        throw "MySQL service installation failed"
    }
    
    Write-Log "MySQL Windows service installation completed" "SUCCESS"
    
    # Start service
    Write-Log "Starting MySQL service..." "INFO"
    try {
        Start-Service MySQL -ErrorAction Stop
        Write-Log "MySQL service started successfully" "SUCCESS"
    }
    catch {
        Write-Log "Failed to start MySQL service: $($_.Exception.Message)" "ERROR"
        throw
    }
    
    # Wait for service to fully start
    Write-Log "Waiting for MySQL service to fully start..." "INFO"
    Start-Sleep -Seconds 15
    
    # Set root password
    Write-Log "Setting root password..." "INFO"
    try {
        $mysqlPath = Join-Path $actualMySQLPath "bin\mysql.exe"
        & $mysqlPath -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '$RootPassword';"
        Write-Log "Root password set successfully" "SUCCESS"
    }
    catch {
        Write-Log "Failed to set root password: $($_.Exception.Message)" "ERROR"
        throw
    }
    
    # Configure Chinese support after installation
    Write-Log "Configuring Chinese character support..." "INFO"
    try {
        $chineseConfigSQL = @"
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_unicode_ci';
SET GLOBAL character_set_database = 'utf8mb4';
SET GLOBAL character_set_client = 'utf8mb4';
SET GLOBAL character_set_connection = 'utf8mb4';
SET GLOBAL character_set_results = 'utf8mb4';
"@
        & $mysqlPath -u root -p$RootPassword -e $chineseConfigSQL
        Write-Log "Chinese character support configured successfully" "SUCCESS"
    }
    catch {
        Write-Log "Failed to configure Chinese character support: $($_.Exception.Message)" "WARN"
    }
    
    # Test Chinese character support
    Write-Log "Testing Chinese character support..." "INFO"
    try {
        $testChineseSQL = @"
CREATE DATABASE IF NOT EXISTS test_chinese CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test_chinese;
CREATE TABLE IF NOT EXISTS test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    chinese_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    english_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO test_table (chinese_name, english_name) VALUES ('测试中文', 'Test English');
SELECT * FROM test_table;
DROP DATABASE test_chinese;
"@
        & $mysqlPath -u root -p$RootPassword -e $testChineseSQL
        Write-Log "Chinese character support test passed" "SUCCESS"
    }
    catch {
        Write-Log "Chinese character support test failed: $($_.Exception.Message)" "WARN"
    }
    
    # Configure firewall
    Write-Log "Configuring firewall rules..." "INFO"
    try {
        New-NetFirewallRule -DisplayName "MySQL Server" -Direction Inbound -Protocol TCP -LocalPort $Port -Action Allow -ErrorAction SilentlyContinue
        Write-Log "Firewall rules configured successfully" "SUCCESS"
    }
    catch {
        Write-Log "Failed to configure firewall rules: $($_.Exception.Message)" "WARN"
    }
    
    Write-Log "MySQL installation with Chinese support completed!" "SUCCESS"
    Write-Log "Installation path: $actualMySQLPath" "INFO"
    Write-Log "Data path: $DataPath" "INFO"
    Write-Log "Port: $Port" "INFO"
    Write-Log "Root password: $RootPassword" "INFO"
    Write-Log "Character set: utf8mb4" "INFO"
    Write-Log "Collation: utf8mb4_unicode_ci" "INFO"
    
    # Return the actual MySQL path for later use
    return $actualMySQLPath
}

# Main execution logic
try {
    Write-Log "=== MySQL Automated Installation Script with Chinese Support (Local Installer) ===" "INFO"
    Write-Log "Starting MySQL installation process..." "INFO"
    
    # Check administrator privileges
    if (!(Test-Administrator)) {
        Write-Log "Administrator privileges required to run this script" "ERROR"
        Write-Log "Please run PowerShell as Administrator" "ERROR"
        exit 1
    }
    
    Write-Log "Administrator privileges check passed" "SUCCESS"
    
    # Validate environment
    if (!$SkipValidation) {
        Write-Log "Validating deployment environment..." "INFO"
        
        # Check internet connection (optional for local installer)
        if (Test-InternetConnection) {
            Write-Log "Internet connection check passed" "SUCCESS"
        }
        else {
            Write-Log "Internet connection not available, using local installer" "WARN"
        }
        
        # Check disk space
        $diskCheck = Test-DiskSpace -Drive "D:" -RequiredSpaceGB 10
        if (!$diskCheck.HasEnoughSpace) {
            Write-Log "Insufficient disk space: Required ${diskCheck.RequiredSpaceGB}GB, Available ${diskCheck.FreeSpaceGB}GB" "ERROR"
            exit 1
        }
        Write-Log "Disk space check passed: Available ${diskCheck.FreeSpaceGB}GB" "SUCCESS"
    }
    
    # Find MySQL installer in local directory
    $localInstallerPath = Find-MySQLInstaller -InstallerDirectory $InstallerDirectory -MySQLVersion $MySQLVersion
    if (!$localInstallerPath) {
        Write-Log "No MySQL installer found in local directory" "ERROR"
        Write-Log "Please ensure MySQL installer is placed in: $InstallerDirectory" "ERROR"
        Write-Log "Expected filename pattern: mysql-installer-community-$MySQLVersion*.msi" "ERROR"
        exit 1
    }
    
    # Check existing installation
    $existingInstall = Test-MySQLInstallation -MySQLPath $InstallPath
    if ($existingInstall.MySQLExeExists -and !$Force) {
        Write-Log "Existing MySQL installation detected" "WARN"
        Write-Log "Use -Force parameter to force reinstallation" "WARN"
        exit 1
    }
    
    if ($existingInstall.MySQLExeExists -and $Force) {
        Write-Log "Existing MySQL installation detected, will force reinstall" "WARN"
        
        # Stop and remove existing service
        if ($existingInstall.ServiceExists) {
            Write-Log "Stopping existing MySQL service..." "INFO"
            Stop-Service MySQL -Force -ErrorAction SilentlyContinue
            Start-Sleep -Seconds 5
        }
        
        # Remove existing service
        Write-Log "Removing existing MySQL service..." "INFO"
        & "$InstallPath\bin\mysqld.exe" --remove MySQL --defaults-file="$InstallPath\my.ini" 2>$null
        
        # Backup existing data directory
        if (Test-Path $DataPath) {
            $backupPath = "$DataPath.backup.$(Get-Date -Format 'yyyyMMdd_HHmmss')"
            Write-Log "Backing up existing data directory to: $backupPath" "INFO"
            Move-Item $DataPath $backupPath -Force
        }
    }
    
    # Execute installation
    $actualMySQLPath = Install-MySQL -MySQLVersion $MySQLVersion -InstallPath $InstallPath -DataPath $DataPath -Port $Port -RootPassword $RootPassword -InstallerPath $localInstallerPath
    
    # Validate installation
    Write-Log "Validating MySQL installation..." "INFO"
    Start-Sleep -Seconds 5
    
    $finalCheck = Test-MySQLInstallation -MySQLPath $actualMySQLPath
    if ($finalCheck.ServiceRunning) {
        Write-Log "MySQL service is running normally" "SUCCESS"
    }
    else {
        Write-Log "MySQL service is not running normally" "ERROR"
        exit 1
    }
    
    # Test database connection
    Write-Log "Testing database connection..." "INFO"
    try {
        $mysqlPath = Join-Path $actualMySQLPath "bin\mysql.exe"
        & $mysqlPath -u root -p$RootPassword -e "SELECT VERSION();"
        if ($LASTEXITCODE -eq 0) {
            Write-Log "Database connection test successful" "SUCCESS"
        }
        else {
            Write-Log "Database connection test failed" "ERROR"
            exit 1
        }
    }
    catch {
        Write-Log "Database connection test exception: $($_.Exception.Message)" "ERROR"
        exit 1
    }
    
    Write-Log "=== MySQL Installation with Chinese Support Completed ===" "SUCCESS"
    Write-Log "Installation Information:" "INFO"
    Write-Log "  - Version: MySQL $MySQLVersion" "INFO"
    Write-Log "  - Installation Path: $actualMySQLPath" "INFO"
    Write-Log "  - Data Path: $DataPath" "INFO"
    Write-Log "  - Port: $Port" "INFO"
    Write-Log "  - Service Name: MySQL" "INFO"
    Write-Log "  - Character Set: utf8mb4" "INFO"
    Write-Log "  - Collation: utf8mb4_unicode_ci" "INFO"
    Write-Log "  - Connection String: Server=localhost;Port=$Port;Database=mysql;Uid=root;Pwd=$RootPassword;CharSet=utf8mb4;" "INFO"
    Write-Log "  - Installer Source: Local ($localInstallerPath)" "INFO"
    Write-Log "" "INFO"
    Write-Log "Chinese Support Features:" "INFO"
    Write-Log "  - Full UTF-8 support for Chinese characters" "INFO"
    Write-Log "  - Proper collation for Chinese text sorting" "INFO"
    Write-Log "  - Support for Chinese field names and data" "INFO"
    Write-Log "  - Optimized InnoDB settings for Chinese text" "INFO"
    
}
catch {
    Write-Log "Error occurred during installation: $($_.Exception.Message)" "ERROR"
    Write-Log "Error details: $($_.Exception.StackTrace)" "ERROR"
    exit 1
}
